﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using acessaDB;
using Frame;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Estrutura;


namespace Banco
{

    /// <summary>
    /// Summary description for BD_Aluno
    /// </summary>
    public class BD_Aluno
    {
        #region variaveis_globais

        acessaDB.conectaDBMSSQLServer sqlServer = new conectaDBMSSQLServer();
        string conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionLocal"];
        string usuario = string.Empty;

        #endregion

        #region construtor

        public BD_Aluno()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
        }
        public BD_Aluno(string usu_Login)
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ambiente"] == "PROD")
                conectionString = System.Configuration.ConfigurationManager.AppSettings["SQLServerConnectionProd"];
            usuario = usu_Login;
        }

        #endregion

        #region metodos

        public System.Data.DataSet _retorna_Aluno(
                                                string ALUNO_ID,
                                                string PESSOA_ID,
                                                string PROFISSAO_ID,
                                                string MOTIVO_INGRESSO_ID,
                                                string ALU_MOTIVO_INGRESSO,
                                                string ACADEMIA_ID,
                                                string SOLICITANTE_ID
                                                )
        {

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "''";
            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "''";
            if (string.IsNullOrEmpty(PROFISSAO_ID)) PROFISSAO_ID = "''";
            if (string.IsNullOrEmpty(MOTIVO_INGRESSO_ID)) MOTIVO_INGRESSO_ID = "''";
            if (string.IsNullOrEmpty(ALU_MOTIVO_INGRESSO)) ALU_MOTIVO_INGRESSO = "''";
            if (string.IsNullOrEmpty(ACADEMIA_ID)) ACADEMIA_ID = "''";
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "''";

            string sql = "SELECT " +
                                  "ALUNO.ALUNO_ID, " +
                                  "ALUNO.PESSOA_ID, " +
                                  "ALUNO.PROFISSAO_ID, " +
                                  "ALUNO.MOTIVO_INGRESSO_ID, " +
                                  "ALUNO.ALU_MOTIVO_INGRESSO, " +
                                  "ALUNO.ACADEMIA_ID, " +
                                  "ALUNO.SOLICITANTE_ID " +
                            "FROM ALUNO " +
                            "LEFT JOIN PESSOA ON PESSOA.PESSOA_ID = ALUNO.PESSOA_ID " +
                            "LEFT JOIN PROFISSAO ON PROFISSAO.PROFISSAO_ID = ALUNO.PROFISSAO_ID " +
                            "LEFT JOIN SOLICITANTE ON SOLICITANTE.SOLICITANTE_ID = ALUNO.SOLICITANTE_ID " +
                            "WHERE " +
                            "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = '') AND " +
                            "      (ALUNO.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = '') AND " +
                            "      (ALUNO.PROFISSAO_ID = " + PROFISSAO_ID + "  OR " + PROFISSAO_ID + " = '') AND " +
                            "      (ALUNO.MOTIVO_INGRESSO_ID = " + MOTIVO_INGRESSO_ID + "  OR " + MOTIVO_INGRESSO_ID + " = '') AND " +
                            "      (ALUNO.ALU_MOTIVO_INGRESSO = " + ALU_MOTIVO_INGRESSO + "  OR " + ALU_MOTIVO_INGRESSO + " = '') AND " +
                            "      (ALUNO.ACADEMIA_ID = " + ACADEMIA_ID + "  OR " + ACADEMIA_ID + " = '') AND " +
                            "      (ALUNO.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = '') ";

            return sqlServer.retornaDataSet(sql, conectionString);

        }


        public List<ES_Aluno> _obter_Aluno(
                                                string ALUNO_ID,
                                                string PESSOA_ID,
                                                string PROFISSAO_ID,
                                                string MOTIVO_INGRESSO_ID,
                                                string ALU_MOTIVO_INGRESSO,
                                                string ACADEMIA_ID,
                                                string SOLICITANTE_ID,
                                                string ALU_STEXCLUIDO
                                                )
        {

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "''"; else ALUNO_ID = Util.retonarAspas(ALUNO_ID + "%");
            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "''"; else PESSOA_ID = Util.retonarAspas(PESSOA_ID + "%");
            if (string.IsNullOrEmpty(PROFISSAO_ID)) PROFISSAO_ID = "''"; else PROFISSAO_ID = Util.retonarAspas(PROFISSAO_ID + "%");
            if (string.IsNullOrEmpty(MOTIVO_INGRESSO_ID)) MOTIVO_INGRESSO_ID = "''"; else MOTIVO_INGRESSO_ID = Util.retonarAspas(MOTIVO_INGRESSO_ID + "%");
            if (string.IsNullOrEmpty(ALU_MOTIVO_INGRESSO)) ALU_MOTIVO_INGRESSO = "''"; else ALU_MOTIVO_INGRESSO = Util.retonarAspas(ALU_MOTIVO_INGRESSO + "%");
            if (string.IsNullOrEmpty(ACADEMIA_ID)) ACADEMIA_ID = "''"; else ACADEMIA_ID = Util.retonarAspas(ACADEMIA_ID + "%");
            if (string.IsNullOrEmpty(SOLICITANTE_ID)) SOLICITANTE_ID = "''"; else SOLICITANTE_ID = Util.retonarAspas(SOLICITANTE_ID + "%");

            string sql = "SELECT " +
                                  "ALUNO.ALUNO_ID, " +
                                  "ALUNO.PESSOA_ID, " +
                                  "ALUNO.PROFISSAO_ID, " +
                                  "ALUNO.MOTIVO_INGRESSO_ID, " +
                                  "ALUNO.ALU_MOTIVO_INGRESSO, " +
                                  "ALUNO.ACADEMIA_ID, " +
                                  "ALUNO.SOLICITANTE_ID " +
                            "FROM ALUNO " +
                            "LEFT JOIN PESSOA ON PESSOA.PESSOA_ID = ALUNO.PESSOA_ID " +
                            "LEFT JOIN PROFISSAO ON PROFISSAO.PROFISSAO_ID = ALUNO.PROFISSAO_ID " +
                            "LEFT JOIN SOLICITANTE ON SOLICITANTE.SOLICITANTE_ID = ALUNO.SOLICITANTE_ID " +
                            "WHERE " +
                            "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = '') AND " +
                            "      (ALUNO.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = '') AND " +
                            "      (ALUNO.PROFISSAO_ID = " + PROFISSAO_ID + "  OR " + PROFISSAO_ID + " = '') AND " +
                            "      (ALUNO.MOTIVO_INGRESSO_ID = " + MOTIVO_INGRESSO_ID + "  OR " + MOTIVO_INGRESSO_ID + " = '') AND " +
                            "      (ALUNO.ALU_MOTIVO_INGRESSO = " + ALU_MOTIVO_INGRESSO + "  OR " + ALU_MOTIVO_INGRESSO + " = '') AND " +
                            "      (ALUNO.ACADEMIA_ID = " + ACADEMIA_ID + "  OR " + ACADEMIA_ID + " = '') AND " +
                            "      (ALUNO.SOLICITANTE_ID = " + SOLICITANTE_ID + "  OR " + SOLICITANTE_ID + " = '') ";

            List<ES_Aluno> lista = new List<ES_Aluno>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Aluno item = new ES_Aluno();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PROFISSAO_ID = dr["PROFISSAO_ID"].ToString();
                    item.MOTIVO_INGRESSO_ID = dr["MOTIVO_INGRESSO_ID"].ToString();
                    item.ALU_MOTIVO_INGRESSO = dr["ALU_MOTIVO_INGRESSO"].ToString();
                    item.ACADEMIA_ID = dr["ACADEMIA_ID"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public List<ES_Aluno> _obter_Alunogeral(
                                                string ALUNO_ID,
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string BOLSISTA,
                                                string TURMA_ID,                                                
                                                string PES_PAI,
                                                string PES_MAE,
                                                string ORDER_BY_CLAUSE,
                                                string ALU_STEXCLUIDO
                                                )
        {
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "0";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (string.IsNullOrEmpty(BOLSISTA)) BOLSISTA = "''"; else BOLSISTA = Util.retonarAspas(BOLSISTA + "%");
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "0";
            if (string.IsNullOrEmpty(PES_PAI)) PES_PAI = "''"; else PES_PAI = Util.retonarAspas(PES_PAI + "%");
            if (string.IsNullOrEmpty(PES_MAE)) PES_MAE = "''"; else PES_MAE = Util.retonarAspas(PES_MAE + "%");            
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "ALUNO.PES_NOME ";
            if (string.IsNullOrEmpty(ALU_STEXCLUIDO)) ALU_STEXCLUIDO = "-1";

            string sql = "SELECT ALUNO_ID,  " +
                            "    PESSOA_ID,  " +
                            "    PES_NOME,  " +
                            "    CASE WHEN BOLSISTA > 0 THEN 'S' ELSE 'N' END BOLSISTA,   " +
                            "    CASE WHEN BOLSISTA_INTEGRAL > 0 THEN 'S' ELSE 'N' END BOLSISTA_INTEGRAL,   " +
                            "    MATRICULAS,  " +
                            "    PES_PAI,  " +
                            "    PES_MAE, " +
                            "    ISNULL(ALU_STEXCLUIDO,0) ALU_STEXCLUIDO " +
                            "FROM VW_ALUNO_GERAL ALUNO " +
                            "WHERE " +
                            "      (ALUNO.ALUNO_ID             = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND " +
                            "      (ALUNO.PESSOA_ID             = " + PESSOA_ID + "  OR " + PESSOA_ID + " = 0) AND " +
                            "      (ALUNO.PES_NOME LIKE " + PES_NOME + "  OR " + PES_NOME + " = '') AND " +
                            "      (ALUNO.BOLSISTA LIKE " + BOLSISTA + "  OR " + BOLSISTA + " = '') AND ";

            if (TURMA_ID != "0")
                sql += "      (ALUNO.ALUNO_ID IN (SELECT M.ALUNO_ID FROM MATRICULA M WHERE M.TURMA_ID = "+TURMA_ID+")) AND ";

            sql +=
                            "      (ALUNO.PES_PAI LIKE " + PES_PAI + "  OR " + PES_PAI + " = '') AND " +
                            "      (ALUNO.PES_MAE LIKE " + PES_MAE + "  OR " + PES_MAE + " = '') AND " +
                            "      (ISNULL(ALUNO.ALU_STEXCLUIDO,0) = " + ALU_STEXCLUIDO + "  OR " + ALU_STEXCLUIDO + " = -1)  " +
                            " ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Aluno> lista = new List<ES_Aluno>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Aluno item = new ES_Aluno();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.BOLSISTA = ((dr["BOLSISTA"].ToString() == "S")? true : false);
                    item.BOLSISTA_INTEGRAL = ((dr["BOLSISTA_INTEGRAL"].ToString() == "S")? true : false);
                    item.MATRICULAS = dr["MATRICULAS"].ToString();
                    item.PES_PAI = dr["PES_PAI"].ToString();
                    item.PES_MAE = dr["PES_MAE"].ToString();
                    item.ALU_STEXCLUIDO = (dr["ALU_STEXCLUIDO"].ToString() == "0") ? false : true;
                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public List<ES_Aluno> _obter_Aluno_turma(
                                                string ALUNO_ID,
                                                string PESSOA_ID,
                                                string PES_NOME,
                                                string BOLSISTA,
                                                string TURMA_ID,
                                                string PES_PAI,
                                                string PES_MAE,
                                                string USUARIO_ID,
                                                string MATRICULA_ID,                                                
                                                string ORDER_BY_CLAUSE,
                                                string ALU_STEXCLUIDO
                                                )
        {
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(PESSOA_ID)) PESSOA_ID = "0";
            if (string.IsNullOrEmpty(PES_NOME)) PES_NOME = "''"; else PES_NOME = Util.retonarAspas(PES_NOME + "%");
            if (string.IsNullOrEmpty(BOLSISTA)) BOLSISTA = "''"; else BOLSISTA = Util.retonarAspas(BOLSISTA + "%");
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "0";
            if (string.IsNullOrEmpty(MATRICULA_ID)) MATRICULA_ID = "0";
            if (string.IsNullOrEmpty(USUARIO_ID)) USUARIO_ID = "0";
            if (string.IsNullOrEmpty(PES_PAI)) PES_PAI = "''"; else PES_PAI = Util.retonarAspas(PES_PAI + "%");
            if (string.IsNullOrEmpty(PES_MAE)) PES_MAE = "''"; else PES_MAE = Util.retonarAspas(PES_MAE + "%");
            

            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "ALUNO.PES_NOME ";
            if (string.IsNullOrEmpty(ALU_STEXCLUIDO)) ALU_STEXCLUIDO = "-1";

            string sql = "SELECT ALUNO_ID,  " +
                            "    PESSOA_ID,  " +
                            "    PES_NOME,  " +
                            "    CASE WHEN BOLSISTA > 0 THEN 'S' ELSE 'N' END BOLSISTA,   " +
                            "    CASE WHEN BOLSISTA_INTEGRAL > 0 THEN 'S' ELSE 'N' END BOLSISTA_INTEGRAL,   " +
                            "    MATRICULAS,  " +
                            "    PES_PAI,  " +
                            "    PES_MAE, " +
                            "    ISNULL(ALU_STEXCLUIDO,0) ALU_STEXCLUIDO, " +
                            "    MATRICULA_ID, "+
                            "    TURMA_ID, "+
                            "    TUR_DESCRICAO, "+
                            "    USUARIO_ID, "+
                            "    USU_NOME "+
                            "FROM VW_ALUNO_TURMA ALUNO " +
                            "WHERE " +
                            "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND " +
                            "      (ALUNO.PESSOA_ID = " + PESSOA_ID + "  OR " + PESSOA_ID + " = 0) AND " +
                            "      (ALUNO.PES_NOME LIKE " + PES_NOME + "  OR " + PES_NOME + " = '') AND " +
                            "      (ALUNO.BOLSISTA LIKE " + BOLSISTA + "  OR " + BOLSISTA + " = '') AND "+
                            "      (ALUNO.MATRICULA_ID = " + MATRICULA_ID + "  OR " + MATRICULA_ID + " = 0) AND "+
                            "      (ALUNO.TURMA_ID = " + TURMA_ID + "  OR " + TURMA_ID + " = 0) AND "+
                            "      (ALUNO.USUARIO_ID = " + USUARIO_ID + "  OR " + USUARIO_ID + " = 0) AND " +  
                            "      (ALUNO.PES_PAI LIKE " + PES_PAI + "  OR " + PES_PAI + " = '') AND " +
                            "      (ALUNO.PES_MAE LIKE " + PES_MAE + "  OR " + PES_MAE + " = '') AND " +
                            "      (ISNULL(ALUNO.ALU_STEXCLUIDO,0) = " + ALU_STEXCLUIDO + "  OR " + ALU_STEXCLUIDO + " = -1)  " +
                            " ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_Aluno> lista = new List<ES_Aluno>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_Aluno item = new ES_Aluno();
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.BOLSISTA = ((dr["BOLSISTA"].ToString() == "S") ? true : false);
                    item.BOLSISTA_INTEGRAL = ((dr["BOLSISTA_INTEGRAL"].ToString() == "S") ? true : false);
                    item.MATRICULAS = dr["MATRICULAS"].ToString();
                    item.PES_PAI = dr["PES_PAI"].ToString();
                    item.PES_MAE = dr["PES_MAE"].ToString();
                    item.ALU_STEXCLUIDO = (dr["ALU_STEXCLUIDO"].ToString() == "0") ? false : true;
                    item.MATRICULA_ID = dr["MATRICULA_ID"].ToString();
                    item.TURMA_ID = dr["TURMA_ID"].ToString();
                    item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString();
                    item.USUARIO_ID = dr["USUARIO_ID"].ToString();
                    item.USU_NOME = dr["USU_NOME"].ToString();

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public List<ES_DadosAluno> _obter_Dados_Aluno(
                                                string ALUNO_ID, 
		                                        string NOME_ALUNO, 
		                                        string END_LOGRADOURO, 
		                                        string END_NUMERO, 
		                                        string END_COMPLEMENTO, 
		                                        string END_BAIRRO, 
		                                        string END_CIDADE, 
		                                        string END_UF, 
		                                        string END_CEP, 
		                                        string PSC_TELEFONE_RESALUNO, 
		                                        string PSC_TELEFONE_COMALUNO, 
		                                        string PSC_TELEFONE_CELALUNO, 
		                                        string PES_EMAIL, 
		                                        string PES_DTNASCIMENTO, 
		                                        string NOME_PAI, 
		                                        string CPF_PAI, 
		                                        string NOME_MAE, 
		                                        string CPF_MAE, 
		                                        string PSC_TELEFONE_RESPAI, 
		                                        string PSC_TELEFONE_CELPAI, 
		                                        string PSC_TELEFONE_RESMAE, 
		                                        string PSC_TELEFONE_CELMAE,
		                                        string PRF_DESCRICAO_PAI, 
		                                        string PRF_DESCRICAO_MAE, 
		                                        string MAT_DTINCLUSAO, 
		                                        string MAT_DATACOBRANCA, 
                                                string TURMA_ID,
		                                        string MOD_DESCRICAO, 
		                                        string NIV_DESCRICAO, 
		                                        string TUR_DESCRICAO, 
		                                        string ALU_BOLSISTA, 
		                                        string MAT_VRMENSALIDADE, 
		                                        string MAT_ISENTOTXPARTICIPACAO, 
		                                        string MAT_TXPARTICIPACAO, 
		                                        string ALU_MEDICAMENTO, 
		                                        string CNV_DESCRICAO,
                                                string ALU_OBSERVACAO,
                                                string ALU_STEXCLUIDO,
                                                string ORDER_BY_CLAUSE
                                                )
        {
            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "0";
            if (string.IsNullOrEmpty(NOME_ALUNO)) NOME_ALUNO = "''";
            if (string.IsNullOrEmpty(END_LOGRADOURO)) END_LOGRADOURO = "''";
            if (string.IsNullOrEmpty(END_NUMERO)) END_NUMERO = "''";
            if (string.IsNullOrEmpty(END_COMPLEMENTO)) END_COMPLEMENTO = "''";
            if (string.IsNullOrEmpty(END_BAIRRO)) END_BAIRRO = "''";
            if (string.IsNullOrEmpty(END_CIDADE)) END_CIDADE = "''";
            if (string.IsNullOrEmpty(END_UF)) END_UF = "''";
            if (string.IsNullOrEmpty(END_CEP)) END_CEP = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_RESALUNO)) PSC_TELEFONE_RESALUNO = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_COMALUNO)) PSC_TELEFONE_COMALUNO = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_CELALUNO)) PSC_TELEFONE_CELALUNO = "''";
            if (string.IsNullOrEmpty(PES_EMAIL)) PES_EMAIL = "''";
            if (string.IsNullOrEmpty(PES_DTNASCIMENTO)) PES_DTNASCIMENTO = "''";
            if (string.IsNullOrEmpty(NOME_PAI)) NOME_PAI = "''";
            if (string.IsNullOrEmpty(CPF_PAI)) CPF_PAI = "''";
            if (string.IsNullOrEmpty(NOME_MAE)) NOME_MAE = "''";
            if (string.IsNullOrEmpty(CPF_MAE)) CPF_MAE = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_RESPAI)) PSC_TELEFONE_RESPAI = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_CELPAI)) PSC_TELEFONE_CELPAI = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_RESMAE)) PSC_TELEFONE_RESMAE = "''";
            if (string.IsNullOrEmpty(PSC_TELEFONE_CELMAE)) PSC_TELEFONE_CELMAE = "''";
            if (string.IsNullOrEmpty(PRF_DESCRICAO_PAI)) PRF_DESCRICAO_PAI = "''";
            if (string.IsNullOrEmpty(PRF_DESCRICAO_MAE)) PRF_DESCRICAO_MAE = "''";
            if (string.IsNullOrEmpty(MAT_DTINCLUSAO)) MAT_DTINCLUSAO = "''";
            if (string.IsNullOrEmpty(MAT_DATACOBRANCA)) MAT_DATACOBRANCA = "''";
            if (string.IsNullOrEmpty(TURMA_ID)) TURMA_ID = "0";
            if (string.IsNullOrEmpty(MOD_DESCRICAO)) MOD_DESCRICAO = "''";
            if (string.IsNullOrEmpty(NIV_DESCRICAO)) NIV_DESCRICAO = "''";
            if (string.IsNullOrEmpty(NIV_DESCRICAO)) NIV_DESCRICAO = "''";
            if (string.IsNullOrEmpty(ALU_BOLSISTA)) ALU_BOLSISTA = "''";
            if (string.IsNullOrEmpty(MAT_VRMENSALIDADE)) MAT_VRMENSALIDADE = "''";
            if (string.IsNullOrEmpty(MAT_ISENTOTXPARTICIPACAO)) MAT_ISENTOTXPARTICIPACAO = "''";
            if (string.IsNullOrEmpty(MAT_TXPARTICIPACAO)) MAT_TXPARTICIPACAO = "''";
            if (string.IsNullOrEmpty(ALU_MEDICAMENTO)) ALU_MEDICAMENTO = "''";
            if (string.IsNullOrEmpty(CNV_DESCRICAO)) CNV_DESCRICAO = "''";
            if (string.IsNullOrEmpty(ALU_OBSERVACAO)) ALU_OBSERVACAO = "''";
            if (string.IsNullOrEmpty(ALU_STEXCLUIDO)) ALU_STEXCLUIDO = "-1";
            if (string.IsNullOrEmpty(ORDER_BY_CLAUSE)) ORDER_BY_CLAUSE = "NOME_ALUNO";

            string sql = "SELECT DISTINCT "+
		                 "           ALUNO_ID,  "+
		                 "           NOME_ALUNO,  "+
		                 "           END_LOGRADOURO, "+ 
		                 "           END_NUMERO,  "+ 
		                 "           END_COMPLEMENTO, "+  
		                 "           END_BAIRRO,  "+ 
		                 "           END_CIDADE,  "+ 
		                 "           END_UF,  "+ 
		                 "           END_CEP,  "+ 
		                 "           PSC_TELEFONE_RESALUNO,  "+ 
		                 "           PSC_TELEFONE_COMALUNO,  "+ 
		                 "           PSC_TELEFONE_CELALUNO,  "+ 
		                 "           PES_EMAIL,  "+ 
		                 "           PES_DTNASCIMENTO,  "+ 
		                 "           NOME_PAI,  "+ 
		                 "           CPF_PAI,  "+ 
		                 "           NOME_MAE,  "+ 
		                 "           CPF_MAE,  "+ 
		                 "           PSC_TELEFONE_RESPAI,  "+ 
		                 "           PSC_TELEFONE_CELPAI,  "+ 
		                 "           PSC_TELEFONE_RESMAE,  "+ 
		                 "           PSC_TELEFONE_CELMAE, "+ 
		                 "           PRF_DESCRICAO_PAI,  "+ 
		                 "           PRF_DESCRICAO_MAE,  "+ 
		                 "           MAT_DTINCLUSAO,  "+ 
		                 "           MAT_DATACOBRANCA,  "+ 
		                 "           MOD_DESCRICAO,  "+ 
		                 "           NIV_DESCRICAO,  "+ 
		                 "           TUR_DESCRICAO,  "+ 
		                 "           ALU_BOLSISTA,  "+ 
		                 "           MAT_VRMENSALIDADE, "+  
		                 "           MAT_ISENTOTXPARTICIPACAO,  "+ 
		                 "           MAT_TXPARTICIPACAO,  "+ 
		                 "           ALU_MEDICAMENTO,  "+ 
		                 "           CNV_DESCRICAO,  "+
                         "           CAST(ALU_OBSERVACAO AS VARCHAR(4000)) ALU_OBSERVACAO, " + 
                         "           MATRICULAS "+   
                         "FROM DBO.VW_RPTDADOSALUNOS ALUNO " +
                         "WHERE " +
                         "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = 0) AND " +
                         "      (ALUNO.TURMA_ID = " + TURMA_ID + "  OR " + TURMA_ID + " = 0) AND " + 
                         "      (ISNULL(ALUNO.ALU_STEXCLUIDO,0) = " + ALU_STEXCLUIDO + "  OR " + ALU_STEXCLUIDO + " = -1)  " +
                         " ORDER BY " + ORDER_BY_CLAUSE;

            List<ES_DadosAluno> lista = new List<ES_DadosAluno>();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    ES_DadosAluno item = new ES_DadosAluno();
                    
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString(); 
		            item.NOME_ALUNO = dr["NOME_ALUNO"].ToString(); 
		            item.END_LOGRADOURO = dr["END_LOGRADOURO"].ToString(); 
		            item.END_NUMERO = dr["END_NUMERO"].ToString(); 
		            item.END_COMPLEMENTO = dr["END_COMPLEMENTO"].ToString(); 
		            item.END_BAIRRO = dr["END_BAIRRO"].ToString(); 
		            item.END_CIDADE = dr["END_CIDADE"].ToString(); 
		            item.END_UF = dr["END_UF"].ToString(); 
		            item.END_CEP = dr["END_CEP"].ToString(); 
		            item.PSC_TELEFONE_RESALUNO = dr["PSC_TELEFONE_RESALUNO"].ToString(); 
		            item.PSC_TELEFONE_COMALUNO = dr["PSC_TELEFONE_COMALUNO"].ToString(); 
		            item.PSC_TELEFONE_CELALUNO = dr["PSC_TELEFONE_CELALUNO"].ToString(); 
		            item.PES_EMAIL = dr["PES_EMAIL"].ToString(); 
		            item.PES_DTNASCIMENTO = dr["PES_DTNASCIMENTO"].ToString(); 
		            item.NOME_PAI = dr["NOME_PAI"].ToString(); 
		            item.CPF_PAI = dr["CPF_PAI"].ToString(); 
		            item.NOME_MAE = dr["NOME_MAE"].ToString(); 
		            item.CPF_MAE = dr["CPF_MAE"].ToString(); 
		            item.PSC_TELEFONE_RESPAI = dr["PSC_TELEFONE_RESPAI"].ToString(); 
		            item.PSC_TELEFONE_CELPAI = dr["PSC_TELEFONE_CELPAI"].ToString(); 
		            item.PSC_TELEFONE_RESMAE = dr["PSC_TELEFONE_RESMAE"].ToString(); 
		            item.PSC_TELEFONE_CELMAE = dr["PSC_TELEFONE_CELMAE"].ToString();
		            item.PRF_DESCRICAO_PAI = dr["PRF_DESCRICAO_PAI"].ToString(); 
		            item.PRF_DESCRICAO_MAE = dr["PRF_DESCRICAO_MAE"].ToString(); 
		            item.MAT_DTINCLUSAO = dr["MAT_DTINCLUSAO"].ToString(); 
		            item.MAT_DATACOBRANCA = dr["MAT_DATACOBRANCA"].ToString(); 
		            item.MOD_DESCRICAO = dr["MOD_DESCRICAO"].ToString(); 
		            item.NIV_DESCRICAO = dr["NIV_DESCRICAO"].ToString(); 
		            item.TUR_DESCRICAO = dr["TUR_DESCRICAO"].ToString(); 
		            item.ALU_BOLSISTA = dr["ALU_BOLSISTA"].ToString(); 
		            item.MAT_VRMENSALIDADE = dr["MAT_VRMENSALIDADE"].ToString(); 
		            item.MAT_ISENTOTXPARTICIPACAO = dr["MAT_ISENTOTXPARTICIPACAO"].ToString(); 
		            item.MAT_TXPARTICIPACAO = dr["MAT_TXPARTICIPACAO"].ToString(); 
		            item.ALU_MEDICAMENTO = dr["ALU_MEDICAMENTO"].ToString(); 
		            item.CNV_DESCRICAO = dr["CNV_DESCRICAO"].ToString();
                    item.ALU_OBSERVACAO = dr["ALU_OBSERVACAO"].ToString();
                    item.MATRICULAS = dr["MATRICULAS"].ToString(); 

                    lista.Add(item);

                }
                return lista;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }

        public ES_Aluno _obter_Aluno_by_id(string ALUNO_ID)
        {

            if (string.IsNullOrEmpty(ALUNO_ID)) ALUNO_ID = "'0'";

            string sql = "SELECT " +
                                  "ALUNO.ALUNO_ID, " +
                                  "ALUNO.PESSOA_ID, " +
                                  "ALUNO.PROFISSAO_ID, " +
                                  "ALUNO.MOTIVO_INGRESSO_ID, " +
                                  "ALUNO.ALU_MOTIVO_INGRESSO, " +
                                  "ALUNO.ACADEMIA_ID, " +
                                  "ALUNO.SOLICITANTE_ID, " +
                                  "PESSOA.PES_NOME, " +
                                  "PESSOA.PES_EMAIL, " +
	                              "PESSOA.PES_DTNASCIMENTO, " +
                                  "CASE WHEN (DATEPART(MONTH, CURRENT_TIMESTAMP) - DATEPART(MONTH, PESSOA.PES_DTNASCIMENTO)) >= 0  " +
                                  "          THEN DATEDIFF(YEAR, PESSOA.PES_DTNASCIMENTO, CURRENT_TIMESTAMP)			  " +
                                  "      ELSE (DATEDIFF(YEAR, PESSOA.PES_DTNASCIMENTO, CURRENT_TIMESTAMP)) - 1 END AS PES_IDADEANOCALC,	        	 " +
                                  "CASE WHEN (DATEPART(MONTH, CURRENT_TIMESTAMP) - DATEPART(MONTH, PESSOA.PES_DTNASCIMENTO)) > 0  " +
                                  "          THEN (DATEPART(MONTH, CURRENT_TIMESTAMP) - DATEPART(MONTH, PESSOA.PES_DTNASCIMENTO)) " +
                                  "      ELSE (DATEPART(MONTH, CURRENT_TIMESTAMP) - DATEPART(MONTH, PESSOA.PES_DTNASCIMENTO))+12 END PES_IDADEMESCALC, " +
	                              "PESSOA.PES_RG, " +
	                              "PESSOA.PES_CPF, " +
                                  "PESSOA.PES_ESTADOCIVIL, " +
                                  "ALUNO.ALU_MEDICAMENTO, "+
                                  "ALUNO.CONVENIO_ID, "+
                                  "ALUNO.ALU_INDICAOMEDICA, "+
                                  "ISNULL(ALUNO.ALU_STEXCLUIDO, 0) ALU_STEXCLUIDO, " +
                                  "DBO.FN_ALUNO_MATRICULAS (ALUNO.ALUNO_ID) MATRICULAS_ALUNO " +
                            "FROM ALUNO " +
                            "LEFT JOIN PESSOA ON PESSOA.PESSOA_ID = ALUNO.PESSOA_ID " +
                            "LEFT JOIN PROFISSAO ON PROFISSAO.PROFISSAO_ID = ALUNO.PROFISSAO_ID " +
                            "LEFT JOIN SOLICITANTE ON SOLICITANTE.SOLICITANTE_ID = ALUNO.SOLICITANTE_ID " +
                            "WHERE " +
                            "      (ALUNO.ALUNO_ID = " + ALUNO_ID + "  OR " + ALUNO_ID + " = '0') ";

            ES_Aluno item = new ES_Aluno();
            SqlConnection sqlConexao = new SqlConnection(conectionString);
            SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
            try
            {
                while (dr.Read())
                {
                    item.ALUNO_ID = dr["ALUNO_ID"].ToString();
                    item.PESSOA_ID = dr["PESSOA_ID"].ToString();
                    item.PROFISSAO_ID = dr["PROFISSAO_ID"].ToString();
                    item.MOTIVO_INGRESSO_ID = dr["MOTIVO_INGRESSO_ID"].ToString();
                    item.ALU_MOTIVO_INGRESSO = dr["ALU_MOTIVO_INGRESSO"].ToString();
                    item.ACADEMIA_ID = dr["ACADEMIA_ID"].ToString();
                    item.SOLICITANTE_ID = dr["SOLICITANTE_ID"].ToString();
                    item.PES_NOME = dr["PES_NOME"].ToString();
                    item.PES_EMAIL = dr["PES_EMAIL"].ToString();
                    item.PES_DTNASCIMENTO = dr["PES_DTNASCIMENTO"].ToString();
                    item.PES_IDADEANOCALC = dr["PES_IDADEANOCALC"].ToString();
                    item.PES_IDADEMESCALC = dr["PES_IDADEMESCALC"].ToString();
                    item.PES_RG = dr["PES_RG"].ToString();
                    item.PES_CPF = dr["PES_CPF"].ToString();
                    item.PES_ESTADOCIVIL = dr["PES_ESTADOCIVIL"].ToString();
                    item.ALU_MEDICAMENTO = dr["ALU_MEDICAMENTO"].ToString();
                    item.CONVENIO_ID = dr["CONVENIO_ID"].ToString();
                    item.ALU_INDICAOMEDICA = dr["ALU_INDICAOMEDICA"].ToString();
                    item.ALU_STEXCLUIDO = (dr["ALU_STEXCLUIDO"].ToString() == "0") ? false : true;
                    item.MATRICULAS = dr["MATRICULAS_ALUNO"].ToString();
                }
                return item;
            }
            finally
            {
                sqlServer.fecharConexao(sqlConexao);
            }
        }


        public void _inserir_Aluno(
                                                string ALUNO_ID,
                                                string PESSOA_ID,
                                                string PROFISSAO_ID,
                                                string MOTIVO_INGRESSO_ID,
                                                string ALU_MOTIVO_INGRESSO,
                                                string ACADEMIA_ID,
                                                string SOLICITANTE_ID
                                                )
        {

            string sql = " INSERT INTO ALUNO " +
                         "      ( " +
                         "          PESSOA_ID, " +
                         "          PROFISSAO_ID, " +
                         "          MOTIVO_INGRESSO_ID, " +
                         "          ALU_MOTIVO_INGRESSO, " +
                         "          ACADEMIA_ID, " +
                         "          SOLICITANTE_ID  " +
                 "      )" +
                 "  VALUES " +
                 "      ( " +
                              Util.retonarAspas(PESSOA_ID, true) + ", " +
                              Util.retonarAspas(PROFISSAO_ID, true) + ", " +
                              Util.retonarAspas(MOTIVO_INGRESSO_ID, true) + ", " +
                              Util.retonarAspas(ALU_MOTIVO_INGRESSO, true) + ", " +
                              Util.retonarAspas(ACADEMIA_ID, true) + ", " +
                              Util.retonarAspas(SOLICITANTE_ID, true) +
                 "      )";
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }


        public string SP_INSERIR_ALUNO(string ALUNO_ID,
                                      string PESSOA_ID,
                                      string PROFISSAO_ID,
                                      string MOTIVO_INGRESSO_ID,
                                      string ALU_MOTIVO_INGRESSO,
                                      string ACADEMIA_ID,
                                      string SOLICITANTE_ID,
                                      string PESSOA_PES_NOME,
                                      string PESSOA_PES_DTNASCIMENTO,
                                      string PESSOA_PES_RG,
                                      string PESSOA_PES_CPF,
                                      string PES_ESTADOCIVIL,
                                      string ALU_MEDICAMENTO,
                                      string CONVENIO_ID,
                                      string ALU_INDICAOMEDICA,
                                      string @PSC_TELEFONE_RESP_R,
                                      string @PSC_TELEFONE_RESP_M
                                                )
        {
            string result = "";
            try
            {

                string sql = "EXECUTE [dbo].[SP_INSERIR_ALUNO] " +
                                                    Util.retonarNull(ALUNO_ID) + ", " +
                                                    Util.retonarNull(PROFISSAO_ID) + ", " +
                                                    Util.retonarNull(MOTIVO_INGRESSO_ID) + ", " +
                                                    Util.retonarAspas(ALU_MOTIVO_INGRESSO, true) + ", " +
                                                    Util.retonarNull(ACADEMIA_ID) + ", " +
                                                    Util.retonarNull(SOLICITANTE_ID) + ", " +
                                                    Util.retonarAspas(PESSOA_PES_NOME, true) + ", " +
                                                    Util.retonarAspas(Util.formataData(PESSOA_PES_DTNASCIMENTO, "dd/MM/yyyy"), true) + ", " +
                                                    Util.retonarAspas(PESSOA_PES_RG, true) + ", " +
                                                    Util.retonarAspas(PESSOA_PES_CPF, true) + ", " +
                                                    Util.retonarAspas(PES_ESTADOCIVIL, true) + ", " +
                                                    Util.retonarAspas(ALU_MEDICAMENTO, true) + ", " +
                                                    Util.retonarNull(CONVENIO_ID) + ", " +
                                                    Util.retonarAspas(ALU_INDICAOMEDICA, true) + ", " +
                                                    Util.retonarAspas(@PSC_TELEFONE_RESP_R, true) + ", " +
                                                    Util.retonarAspas(@PSC_TELEFONE_RESP_M, true) + ", " +
                                                    Util.retonarNull(PESSOA_ID);
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);


                //trazer ultima matricula
                sql = "SELECT ALUNO.ALUNO_ID " +
                      "  FROM ALUNO  " +
                      "  INNER JOIN PESSOA ON PESSOA.PESSOA_ID = ALUNO.PESSOA_ID  " +
                      "  WHERE (PESSOA.PES_NOME = " + Util.retonarAspas(PESSOA_PES_NOME, true) + ")  " +
                      "  AND (PESSOA.PES_DTNASCIMENTO = " + Util.retonarAspas(Util.formataData(PESSOA_PES_DTNASCIMENTO, "dd/MM/yyyy"), true) + ") ";/* +
                      "  AND (PESSOA.PES_RG = " + Util.retonarAspas(PESSOA_PES_RG, true) + ") " +
                      "  AND (PESSOA.PES_CPF = " + Util.retonarAspas(PESSOA_PES_CPF, true) + ")";*/

                SqlConnection sqlConexao = new SqlConnection(conectionString);
                SqlDataReader dr = sqlServer.retornaDataReader(sql, conectionString, sqlConexao);
                try
                {
                    while (dr.Read())
                    {
                        result = dr["ALUNO_ID"].ToString();
                    }
                }
                catch { }

                return result;
            }
            catch (Exception e)
            {
                return null;
            }
        }
        
        public void _atualizar_Aluno(
                                      string ALUNO_ID, 
                                      string PESSOA_ID, 
                                      string PROFISSAO_ID, 
                                      string MOTIVO_INGRESSO_ID, 
                                      string ALU_MOTIVO_INGRESSO, 
                                      string ACADEMIA_ID, 
                                      string SOLICITANTE_ID,                                       
                                      string ALU_MEDICAMENTO, 
                                      string CONVENIO_ID, 
                                      string ALU_INDICAOMEDICA,
                                      bool ALU_STEXCLUIDO  
                                                )
        {
            //dados do Aluno
            string sql = " UPDATE ALUNO SET " +                         
                         "             PESSOA_ID  = " + Util.retonarNull(PESSOA_ID) + ", " + 
                         "             PROFISSAO_ID  = " + Util.retonarNull(PROFISSAO_ID) + ", " + 
                         "             MOTIVO_INGRESSO_ID  = " + Util.retonarNull(MOTIVO_INGRESSO_ID) + ", " + 
                         "             ALU_MOTIVO_INGRESSO  = " + Util.retonarAspas(ALU_MOTIVO_INGRESSO, true) + ", " + 
                         "             ACADEMIA_ID  = " + Util.retonarNull(ACADEMIA_ID) + ", " + 
                         "             SOLICITANTE_ID  = " + Util.retonarNull(SOLICITANTE_ID) + ", " +                          
                         "             ALU_MEDICAMENTO  = " + Util.retonarAspas(ALU_MEDICAMENTO, true) + ", " + 
                         "             CONVENIO_ID  = " + Util.retonarNull(CONVENIO_ID) + ", " +
                         "             ALU_INDICAOMEDICA   = " + Util.retonarAspas(ALU_INDICAOMEDICA, true) + ", " +
                         "             ALU_STEXCLUIDO   = " + ((ALU_STEXCLUIDO == true)? "1" : "0") + 
                         " WHERE ALUNO_ID =  " + ALUNO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
                        
        }



        public void _excluir_Aluno(string ALUNO_ID)
        {
            string sql = "DELETE FROM ALUNO WHERE ALUNO_ID = " + ALUNO_ID;
            try
            {
                sqlServer._ExecutarComandoRetornandoID(sql, conectionString, false, usuario);
            }
            catch
            { }
        }



        #endregion
    }
}
